﻿using Enter.Utils;
using Enter.Utils.EnumExt;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Enter.Excel
{
    public static class ExcelExtend
    {
        static ExcelExtend()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }
      
        public static (List<T> DataList, List<ComposeAttribute> ExcelColumns, List<string> Errors) ToList<T>(string filePath, bool strict = false, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            return ToList<T>(filePath, ComposeExtend.Headers<T>(), strict, drops);
        }

        public static (List<T> DataList, List<ComposeAttribute> ExcelColumns, List<string> Errors) ToList<T>(string filePath, List<ComposeAttribute> headers, bool strict = true, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.First(t => t.Hidden == eWorkSheetHidden.Visible);
                return ToList<T>(worksheet, headers, strict, drops);
            }
        }

        /// <summary>
        /// 导入
        /// </summary>
        private static (List<T> DataList, List<ComposeAttribute> ExcelColumns, List<string> Errors) ToList<T>(ExcelWorksheet worksheet, List<ComposeAttribute> all_headers, bool strict = true, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            all_headers = all_headers.Where(t => t.Import).ToList();
            // 获取Excel列名对应的index;
            for (var i = 1; i <= worksheet.Dimension.End.Column; i++)
            {
                var headerName = worksheet.Cells[1, i].Text.Trim();
                var findHeaders = all_headers.Where(p => p.HeaderName == headerName);
                foreach (var h in findHeaders)
                {
                    if (h.Sort < 1) h.Sort = i;
                }
            }
            var headers = all_headers;
            if (strict)
            {
                var notFind = all_headers.Where(t => t.Sort < 1);
                if (notFind.Any())
                {
                    throw new Exception(string.Format("模板错误.未找到列 ‘{0}’", string.Join(",", notFind.Select(t => t.HeaderName))));
                }
            }
            else
            {
                headers = all_headers.Where(t => t.Sort > 0).ToList();
            }
            var t_type = typeof(T);
            // 检查Property;
            headers.ForEach(h =>
            {
                if (h.Property == null) h.Property = t_type.GetProperty(h.PropertyName);
            });
            List<T> result = new List<T>();
            List<string> errors = new List<string>();
            // 读取行;
            for (var r = 2; r <= worksheet.Dimension.End.Row; r++)
            {
                T temp = (T)Activator.CreateInstance(t_type);
                foreach (var header in headers)
                {
                    object obj = null;
                    var cell = worksheet.Cells[r, header.Sort];
                    var cell_string = cell.Text.Trim();
                    if (header.Property.PropertyType.IsEnum)
                    {
                        try
                        {
                            if (string.IsNullOrWhiteSpace(cell_string))
                            {
                                obj = header.Property.PropertyType.IsValueType ? Activator.CreateInstance(header.Property.PropertyType) : null;
                            }
                            else
                            {
                                obj = Enum.Parse(header.Property.PropertyType, cell_string);
                            }
                        }
                        catch
                        {
                            errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}), 选项错误.", r, header.HeaderName, ToName(header.Sort), cell_string));
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrWhiteSpace(header.Drop) && !string.IsNullOrWhiteSpace(cell_string))
                        {
                            var item = drops[header.Drop].Where(t => t.Text == cell_string).FirstOrDefault();
                            if (item == null)
                            {
                                errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}), 选项错误.", r, header.HeaderName, ToName(header.Sort), cell_string));
                                continue;
                            }
                            else
                            {
                                cell_string = item.Value;
                            }

                        }
                        switch (Type.GetTypeCode(header.Property.PropertyType))
                        {
                            case TypeCode.Int32:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0;
                                }
                                else
                                {
                                    if (int.TryParse(cell_string, out int v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是整数类型.", r, header.HeaderName, ToName(header.Sort), cell_string));
                                    }
                                }
                                break;
                            case TypeCode.Int64:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0L;
                                }
                                else
                                {
                                    if (long.TryParse(cell_string, out long v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是整数类型.", r, header.HeaderName, ToName(header.Sort), cell_string));
                                    }
                                }
                                break;
                            case TypeCode.Decimal:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = 0M;
                                }
                                else
                                {
                                    if (decimal.TryParse(cell_string, out decimal v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是数字类型.", r, header.HeaderName, ToName(header.Sort), cell_string));
                                    }
                                }
                                break;
                            case TypeCode.String:
                                obj = cell_string;
                                break;
                            case TypeCode.DateTime:
                                if (string.IsNullOrWhiteSpace(cell_string))
                                {
                                    obj = DateTime.MinValue;
                                }
                                else
                                {
                                    if (DateTime.TryParse(cell_string, out DateTime v))
                                    {
                                        obj = v;
                                    }
                                    else
                                    {
                                        var val = cell.Style.Numberformat.Format;
                                        var notDateTime = true;
                                        if (cell.Value.GetType() == typeof(double))
                                        {
                                            try
                                            {
                                                obj = DateTime.FromOADate((double)cell.Value);
                                                notDateTime = false;
                                            }
                                            catch { }
                                        }
                                        if (notDateTime)
                                        {
                                            errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),应该是时间格式.", r, header.HeaderName, ToName(header.Sort), cell_string));
                                        }
                                    }
                                }
                                break;
                            case TypeCode.Boolean:
                                if (cell_string == "是" || cell_string.ToLower() == "true" || cell_string == "1" || cell_string == "有")
                                {
                                    obj = true;
                                }
                                else
                                {
                                    obj = false;
                                }
                                break;
                            default:
                                try
                                {
                                    obj = Convert.ChangeType(cell_string, header.Property.PropertyType);
                                }
                                catch
                                {
                                    errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),数据类型错误({4}).", r, header.HeaderName, ToName(header.Sort), cell_string, header.Property.PropertyType));
                                }
                                break;

                        }
                    }
                    try
                    {
                        header.Property.SetValue(temp, obj);
                    }
                    catch (Exception e)
                    {
                        errors.Add(string.Format("第{0}行,{1}({2})列(内容：{3}),转换失败({4}).", r, header.HeaderName, ToName(header.Sort), cell_string, e.Message));
                    }

                }
                result.Add(temp);
            }
            return (result, headers, errors);

        }

        public static byte[] Save<T>(List<T> data, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                Save(package, data, ComposeExtend.Headers<T>(), drops);
                //package.SaveAs(stream);
                return package.GetAsByteArray();
            }
        }

        public static void Save<T>(FileInfo fileInfo, List<T> data, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                Save(package, data, ComposeExtend.Headers<T>(), drops);
                package.SaveAs(fileInfo);
            }
        }

        public static void Save<T>(FileInfo fileInfo, List<T> data, IEnumerable<string> columns = null, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                var all_headers = ComposeExtend.Headers<T>().Where(t => t.Export).ToList();
                if (columns == null || columns.Count() == 0)
                {
                    Save(package, data, all_headers, drops);
                }
                else
                {
                    List<ComposeAttribute> headers = new List<ComposeAttribute>();
                    foreach (var col in columns)
                    {
                        var colHeader = all_headers.First(t => t.PropertyName == col);
                        colHeader.Sort = 0;
                        headers.Add(colHeader);
                    }
                    Save(package, data, headers, drops);
                }
                package.SaveAs(fileInfo);
            }
        }

        public static ExcelPackage Save<T>(ExcelPackage package, List<T> data, List<ComposeAttribute> headers, IDictionary<string, List<EnumSelectListItem>> drops = null)
        {
            var t_type = typeof(T);
            // 获取头部.
            if (headers.Any(t => t.Sort > 0))
            {
                // 如果存在排序号, 则排序.
                headers = headers.OrderBy(t => t.Sort).ToList();
            }
            // 检查Property;
            headers.ForEach(h =>
            {
                if (h.Property == null) h.Property = t_type.GetProperty(h.PropertyName);
            });
            var dropSheetDic = new Dictionary<string, (int Column, int Length)>();
            ExcelWorksheet dropSheet = null;
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");
            // 头部.
            for (var y = 0; y < headers.Count; y++)
            {
                var header = headers[y];
                worksheet.Cells[1, y + 1].Value = header.HeaderName;
                // 字体.
                worksheet.Cells[1, y + 1].Style.Font.Bold = true;
                // 必填;
                if (header.Required) worksheet.Cells[1, y + 1].Style.Font.Color.SetColor(Color.White);
                List<EnumSelectListItem> drop = null;
                // 下拉列表
                if (!string.IsNullOrEmpty(header.Drop))
                {
                    drop = drops[header.Drop];
                }
                else if (header.Property.PropertyType.IsEnum)
                {
                    drop = EnumExtend.EnumList(header.Property.PropertyType, false);
                }
                if (drop != null)
                {
                    var listArea = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, y + 1, ExcelPackage.MaxRows, y + 1].Address);
                    listArea.ShowErrorMessage = true;
                    listArea.Error = "请输入正确的选项!";
                    var dropTexts = drop.Select(t => t.Text).ToList();
                    if (string.Join(",", dropTexts).Length > 255)
                    {
                        if (dropSheet == null)
                        {
                            dropSheet = package.Workbook.Worksheets.Add("DropSheet");
                            dropSheet.Hidden = eWorkSheetHidden.VeryHidden;
                        }
                        var r = dropSheetDic.Keys.Count + 1;
                        if (dropSheetDic.ContainsKey(header.Drop))
                        {
                            r = dropSheetDic[header.Drop].Column;
                        }
                        else
                        {
                            dropSheetDic.Add(header.Drop, (r, dropTexts.Count));
                        }
                        var c = 0;
                        dropTexts.ForEach(txt => {
                            c++;
                            dropSheet.Cells[c, r].Value = txt;
                        });
                        listArea.Formula.ExcelFormula = string.Format("=DropSheet!${0}${1}:${2}${3}", ToName(r), 1, ToName(r), c);
                    }
                    else
                    {
                        dropTexts.ForEach(t => listArea.Formula.Values.Add(t));
                    }
                }
            }
            // https://riptutorial.com/epplus/example/26056/number-formatting
            // https://riptutorial.com/epplus/example/26058/date-formatting
            // Text Format worksheet.Cells["A1:A25"].Style.Numberformat.Format = "@";
            // 数据.
            for (var i = 0; i < data.Count; i++)
            {
                for (var y = 0; y < headers.Count; y++)
                {
                    var header = headers[y];                    
                    if (!string.IsNullOrWhiteSpace(header.Drop))
                    {
                        worksheet.Cells[i + 2, y + 1].Value = ToolExtend.ToString(header.Property.GetValue(data[i], null), header.Drop, true);
                    }
                    else
                    {
                        worksheet.Cells[i + 2, y + 1].Value = ToolExtend.ToString(header.Property.GetValue(data[i], null), header.Format);
                    }
                }
            }
            return package;
        }
        //Excel列字母转数字
        public static int ToIndex(string columnName, int start = 1)
        {
            if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) { throw new Exception("invalid parameter"); }

            int index = 0;
            char[] chars = columnName.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1 + start;
        }

        //Excel数字转列字母
        public static string ToName(int index, int start = 1)
        {
            index = index - start;
            if (index < 0) { throw new Exception("invalid parameter"); }

            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }
    }
}
